<?php
namespace serv\task\warehouse;

use L;
use of;
use of\base\com\mq;

class stock
{
    //统计汇总库存信息(按国家统计)
    public static function sumByCountry()
    {
        //先清掉旧的数据
        $sql = "TRUNCATE TABLE stock_country;";
        L::sql($sql, 'warehouse');
        //再把统计到的数据更新
        $date = date('Y-m-d H:i:s');
        $uid = of::config('env.SYSTEM_ID');
        $sql = "INSERT INTO stock_country(sku, sku_name, stock, available_stock, lock_stock, on_way_stock, country, created_by, created_at, updated_by, updated_at) 
                    SELECT a.sku, a.sku_name, SUM(a.stock) AS stock, SUM(a.available_stock) AS available_stock, SUM(a.lock_stock) AS lock_stock,
                    SUM(a.on_way_stock) AS on_way_stock, b.country, {$uid} AS created_by, '{$date}' AS created_at, {$uid} AS updated_by, '{$date}' AS updated_at
                FROM `stock` AS a LEFT JOIN warehouse AS b ON a.warehouse_id = b.warehouse_id
                WHERE 1
                GROUP BY country, sku;";
        L::sql($sql, 'warehouse');
    }

    //统计团队维度的库存
    public static function sumByBelong()
    {
        //先清掉旧的数据
        $sql = "TRUNCATE TABLE stock_belong;";
        L::sql($sql, 'warehouse');
        //再把统计到的数据更新
        $date = date('Y-m-d H:i:s');
        $uid = of::config('env.SYSTEM_ID');
        $sql = "INSERT INTO stock_belong(sku, sku_code, sku_name, sku_name_en, belong, stock, available_stock, lock_stock, on_way_stock, on_produce, created_by, created_at, updated_by, updated_at) 
                SELECT a.sku, a.sku_code, a.sku_name, a.sku_name_en, a.belong, SUM(a.stock) AS stock, SUM(a.available_stock) AS available_stock, SUM(a.lock_stock) AS lock_stock,
                SUM(a.on_way_stock) AS on_way_stock, SUM(a.on_produce) AS on_produce, {$uid} AS created_by, '{$date}' AS created_at, {$uid} AS updated_by, '{$date}' AS updated_at
                FROM `stock` AS a 
                WHERE 1
                AND a.`status` = 1
                AND warehouse_id IN (SELECT warehouse_id FROM warehouse WHERE type <> 20)
                GROUP BY sku, belong;";
        L::sql($sql, 'warehouse');
    }

    //统计库存汇总
    public static function stockSummary()
    {
        //先清掉旧的数据
        $sql = "TRUNCATE TABLE stock_summary;";
        L::sql($sql, 'warehouse');

        //统计在库库存，锁库存的总数据
        $sql = "SELECT sku, sku_name, SUM(stock) AS stock, SUM(available_stock) AS available_stock, SUM(lock_stock) AS lock_stock FROM stock WHERE 1 
                AND warehouse_id IN (SELECT warehouse_id FROM warehouse WHERE type = 20 AND warehouse_id NOT IN (13))
                GROUP BY sku";
        $result = L::sql($sql, 'warehouse');
        if (!empty($result)) {
            $rows = array_chunk($result, 50);
            if (!empty($rows)) {
                foreach ($rows as $k => $v) {
                    //把SKU更新到表中进行统计
                    $key = 'stockSummaryQueue_stock_'. $k;
                    mq::set(["stockSummaryQueue", $key, $k * 5], $v);
                }
            }
        }

        //统计采购在途库存的总数据
        $sql = "SELECT a.sku, a.sku_name, SUM(a.qty - a.arrive_qty) AS purchase_total FROM purchase_list AS a 
                LEFT JOIN purchase_order AS b ON a.order_id = b.order_id
                WHERE 1 AND b.status IN (20, 30)
                GROUP BY sku;";
        $result = L::sql($sql, 'purchase');
        if (!empty($result)) {
            $rows = array_chunk($result, 50);
            if (!empty($rows)) {
                foreach ($rows as $k => $v) {
                    //把SKU更新到表中进行统计
                    $key = 'stockSummaryQueue_purchase_'. $k;
                    mq::set(["stockSummaryQueue", $key, $k * 5], $v);
                }
            }
        }

        //统计在中途库存的总数据
        $sql = "SELECT sku, sku_name, SUM(diff_qty) AS shipped_total FROM transfer_container_detail WHERE 1
                AND status IN (20, 30)
                GROUP BY sku;";
        $result = L::sql($sql, 'warehouse');
        if (!empty($result)) {
            $rows = array_chunk($result, 50);
            if (!empty($rows)) {
                foreach ($rows as $k => $v) {
                    //把SKU更新到表中进行统计
                    $key = 'stockSummaryQueue_shipped_'. $k;
                    //echo json_encode($v, JSON_UNESCAPED_UNICODE); exit;
                    mq::set(["stockSummaryQueue", $key, $k * 5], $v);
                }
            }
        }

    }

    //库存变更记录（发货用）
    public static function stockQueue()
    {
        $sql = "SELECT id, order_no, `type`, order_type FROM stock_queue WHERE 1 AND status = 0;";
        $result = L::sql($sql, 'warehouse');
        if (!empty($result)) {
            foreach ($result as $v) {
                $key = 'stockQueue_' . $v['id'];
                //echo json_encode($v); exit;
                mq::set(["stockQueue", $key], $v);
            }
        }
    }


}